﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
using System.IO;
using System.Drawing;


public class Support
{
    //string sqlConnectionString = @"Server=.\sqlexpress; Database=Northwind; integrated security=sspi;";
    string sqlConnectionString = ConfigurationManager.AppSettings["SqlConnectionString"].ToString();

    public List<Category> GetAll()
    {
        
        List<Category> list = new List<Category>();
        SqlConnection cnn = new SqlConnection(sqlConnectionString);
        try
        {
            SqlCommand cmd = cnn.CreateCommand();
            cmd.CommandText = "SELECT CategoryID, CategoryName, Description, Picture FROM Categories";
            cnn.Open();
            SqlDataReader dr = cmd.ExecuteReader();
            
            while (dr.Read())
            {
                byte[] temp = (byte[])dr["Picture"];
                MemoryStream ms = new MemoryStream();
                ms.Write(temp, 78, temp.Length - 78);
                //Category cate = new Category(Int32.Parse(dr["CategoryID"].ToString()), dr["CategoryName"].ToString(), dr["Description"].ToString(), temp);
                Category cate = new Category();
                cate.CategoryID = Int32.Parse(dr["CategoryID"].ToString());
                cate.CategoryName = dr["CategoryName"].ToString();
                cate.Description = dr["Description"].ToString();
                cate.Picture = ms.GetBuffer();

                list.Add(cate);
            }
            dr.Dispose();
            dr.Close();
            return list;
        }
        catch (Exception)
        {
            return null;
        }
        finally
        {
           cnn.Close();
        } 
    }


    public Category Details(int id)
    {
        Category cate = new Category();
        SqlConnection cnn = new SqlConnection(sqlConnectionString);
        try
        {
            SqlCommand cmd = cnn.CreateCommand();
            cmd.CommandText = "SELECT CategoryID, CategoryName, Description, Picture FROM Categories WHERE CategoryID = " + id;
            cnn.Open();
            SqlDataReader dr = cmd.ExecuteReader();

            MemoryStream ms = new MemoryStream();
            if (dr.Read() && dr != null)
            {
                byte[] temp = (byte[])dr[3];
                ms.Write(temp, 78, temp.Length - 78);

                cate = new Category
                {
                    CategoryID = int.Parse(dr[0].ToString()),
                    CategoryName = dr[1].ToString(),
                    Description = dr[2].ToString(),
                    Picture = temp
                };

            }
            dr.Dispose();
            dr.Close();
            return cate;
        }
        catch (Exception)
        {
            return null;
        }
        finally
        {
            cnn.Close();
        }
    }
    //byte[] to image
    public Image ByteArray2Image(byte[] array)
    {
        try
        {
            MemoryStream ms = new MemoryStream(array);
            return Image.FromStream(ms);
        }
        catch (Exception)
        {
            throw;
        }
    }

    private byte[] SubByte(byte[] array)
    {
        byte[] b = new byte[array.Length - 78];
        int x = 0;
        for (int i = 78; i < array.Length; i++)
        {
            b[x++] = array[i];            
        }
        return b;
    }

    public byte[] String2Byte(string value)
    {
        try
        {
            System.Text.UTF8Encoding encode = new System.Text.UTF8Encoding();
            return encode.GetBytes(value);
            
        }
        catch (Exception)
        {
            return null;
        }
    }
}